<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.io.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			<p>Undergraduate remaining degree requirements</p>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						CachedRowSet cachedQuery = new CachedRowSetImpl();
						int minUnits = -1;
						int unitsTaken = 0;
						Map<String, Integer> requirements = null;
						Map<String, Integer> remainingUnits = null;
						//ArrayList<Category> categoryRequirement = new ArrayList<Category>();
						if (action != null && action.equals("submit")) {
							// Begin transaction
							conn.setAutoCommit(false);
							// find out how many units total required for a degree
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT min_units FROM degree_requirement WHERE requirement_id =?");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id"))); 
							ResultSet rs = pstmt.executeQuery();
							if(rs.next())
								minUnits = rs.getInt("min_units");
							
							//find out how many units total required for each category
							pstmt = conn.prepareStatement("SELECT * FROM category WHERE requirement_id = ?");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id"))); 
							rs = pstmt.executeQuery();
							requirements = new HashMap<String, Integer>();
							while(rs.next()){
								requirements.put(rs.getString("category_name"), rs.getInt("min_units"));
							}
							// get the course information from the students
							pstmt = conn.prepareStatement("SELECT category_name, sum(units) AS sum FROM enroll, section, class, category, category_course " +
							 "WHERE student_id = ? AND category.requirement_id = ? AND enroll.section_id = section.section_id AND section.class_id = class.class_id AND " +
							 "class.course_number = category_course.course_number AND category_course.category_id = category.category_id AND " +
							 "enroll.grade IN ('A+','A','A-','B+','B','B-','C+','C','C-','D','S') GROUP BY category_name");
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setInt(2, Integer.parseInt(request.getParameter("requirement_id")));
							rs = pstmt.executeQuery();
							remainingUnits = new HashMap<String, Integer>();
							while(rs.next()){
								unitsTaken += rs.getInt("sum");
								int remainingUnit = requirements.get(rs.getString("category_name")) - rs.getInt("sum");
								remainingUnits.put(rs.getString("category_name"), remainingUnit);
							}
							for (String name : requirements.keySet()) {
								if (!remainingUnits.containsKey(name)) {
									remainingUnits.put(name, requirements.get(name));
								}
							}
							for (String name : remainingUnits.keySet()) {
								System.out.println(name + ": " + remainingUnits.get(name));
							}
							conn.setAutoCommit(true);
						}
			
						// Create the statement
						// Get current quarter.
							InputStream in = application.getResourceAsStream("settings.properties");
							Properties p = new Properties();
							p.load(in);
							String quarter = p.getProperty("quarter");
							int year = Integer.parseInt(p.getProperty("year"));
							
						Statement statement = conn.createStatement();
						
						ResultSet rsStudent = statement.executeQuery("SELECT ssn, student.student_id, first_name, middle_name, last_name FROM student, student_enrollment, undergrad_student " +
								"WHERE student.student_id = student_enrollment.student_id AND student_enrollment.quarter = '" + quarter + "' AND student_enrollment.year = " + year  +
								" AND student.student_id = undergrad_student.student_id");
						CachedRowSet cachedStudent = new CachedRowSetImpl();
						cachedStudent.populate(rsStudent);
						
						ResultSet rsDegree = statement.executeQuery("SELECT * FROM degree_requirement WHERE title = 'BSC'");
						CachedRowSet cachedDegree = new CachedRowSetImpl();
						cachedDegree.populate(rsDegree);

				%>
				<table border="1">
					<tr>
						<form action="report1d.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
								String student_id = request.getParameter("student_id");
								while(cachedStudent.next()){
								%>
									<option value=<%=cachedStudent.getString("student_id")%> 
									<%if(student_id !=null && student_id.equals(cachedStudent.getString("student_id"))) out.println("selected = selected");%> >
									<%=cachedStudent.getString("student_id")%> 
									<%=cachedStudent.getString("first_name")%> 
									<%=cachedStudent.getString("middle_name")%> 
									<%=cachedStudent.getString("last_name")%> 
									SSN:<%=cachedStudent.getString("ssn")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							
							<th>
								<select name="requirement_id" style="width:200px">
								<%
								int requirement_id = 0 ;
								if(request.getParameter("requirement_id")!=null)
								 	requirement_id = Integer.parseInt(request.getParameter("requirement_id"));
								while(cachedDegree.next()){
								%>
									<option value=<%=cachedDegree.getInt("requirement_id")%> 
									<%if(requirement_id ==cachedDegree.getInt("requirement_id")) out.println("selected = selected");%> >
									<%=cachedDegree.getString("title")%> 
									<%=cachedDegree.getString("department")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							
							<th><input type="submit" value="submit"></th>
						</form>
					</tr>
					<% if (remainingUnits != null) { %>
						
					<tr>
						<th>Category Name</th>
						<th>Minimum Units Remaining</th>	
					</tr>
					<%for(String name : remainingUnits.keySet()){%>
					<tr>
						<td><%=name%></td>
						<td><%=remainingUnits.get(name) %></td>
					</tr>
					
					
					<% }%>
					<tr><td>Total Remaining Units: <%=minUnits-unitsTaken%> </td></tr>
					
					<%} %>
					
						
				<% 
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>